with order_summary as (
select
       order_source_code,
       order_source_name,
       customer_code,
       customer_name,
       pick_network_code,
       date(input_time) dt,
       count(distinct waybill_id) num
from jms_dwd.dwd_yl_oms_oms_order_hf
where dt between concat(date_add('{{ execution_date | cst_ds }}',-6),'-00') and concat('{{ execution_date | cst_ds }}','-23')
and date(input_time) between date_add('{{ execution_date | cst_ds }}',-3) and '{{ execution_date | cst_ds }}'
group by
order_source_code,
order_source_name,
customer_code,
customer_name,
pick_network_code,
date(input_time)
), order_expend as (
    select order_summary.*,
           net.name pick_network_name,
           net.agent_code,
           net.agent_name
    from order_summary
    left join jms_dim.dim_network_whole_massage net
    on order_summary.pick_network_code = net.code
)
insert overwrite table jms_dm.dm_tab_business_monitor_summary_dt partition (dt)
select detail.agent_code,
       detail.customer_code,
       detail.order_source_code,
       detail.false_taking_site_code,
       detail.agent_name,
       detail.customer_name,
       detail.order_source_name,
       detail.false_taking_site,
       detail.false_taking_cnt,
       detail.real_taking_cnt,
       detail.network_send_cnt,
       detail.store_arrival_cnt,
       detail.entrepot_send_cnt,
       detail.center_send_cnt,
       detail.center_arrival_cnt,
       detail.send_over_one_day_cnt,
       detail.send_over_two_day_cnt,
       detail.taking_over_half_day_cnt,
       detail.taking_over_one_day_cnt,
       detail.taking_over_two_day_cnt,
       detail.date_time,
       order_expend.num,
       detail.dt
        from (
select agent_code,
       customer_code,
       order_source_code,
       false_taking_site_code,
       agent_name,
       customer_name,
       order_source_name,
       false_taking_site,
       sum(if(scan_type = '快件揽收', 1, 0))       false_taking_cnt,
       sum(if(real_taking_time is not null, 1, 0)) real_taking_cnt,
       sum(
               if(
                               scan_type = '发件扫描'
                           and network_type = '6',
                               1,
                               0
                   )
           )                                       network_send_cnt,
       sum(if(scan_type = '集货到件', 1, 0))       store_arrival_cnt,
       sum(
               if(
                               scan_type = '发件扫描'
                           and network_type = '5',
                               1,
                               0
                   )
           )                                       entrepot_send_cnt,
       sum(
               if(
                               scan_type = '发件扫描'
                           and network_type = '4',
                               1,
                               0
                   )
           )                                       center_send_cnt,
       sum(if(scan_type = '中心到件', 1, 0))       center_arrival_cnt,
       sum(is_send_over_one_day)                   send_over_one_day_cnt,
       sum(is_send_over_two_day)                   send_over_two_day_cnt,
       sum(is_taking_over_half_day)                taking_over_half_day_cnt,
       sum(is_taking_over_one_day)                 taking_over_one_day_cnt,
       sum(is_taking_over_two_day)                 taking_over_two_day_cnt,
       date(dt)                                    date_time,
       dt
from jms_dm.dm_tab_business_monitor_detail_dt
where dt between date_sub('{{ execution_date | cst_ds }}', 3) and '{{ execution_date | cst_ds }}'
group by dt,
         agent_code,
         agent_name,
         customer_code,
         customer_name,
         order_source_code,
         order_source_name,
         false_taking_site_code,
         false_taking_site
) detail left join order_expend
on detail.agent_code = order_expend.agent_code
and detail.customer_code = order_expend.customer_code
and detail.order_source_code = order_expend.order_source_code
and detail.dt = order_expend.dt
and detail.false_taking_site_code = order_expend.pick_network_code
distribute by dt, PMOD(HASH(RAND()), 2);
